﻿Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.Office.Interop
Imports System.Data
Imports System.ComponentModel
Imports System.Threading

Public Class Form1
    Private DataSource As String = "Data Source="
    Private InitialCatalog As String = ";Initial Catalog="
    Private IntegratedSecurity As String = ";Integrated Security=True"
    Private WithEvents MyDAL As DAL

#Region " UI Update Thread Crap "
    Delegate Sub SetFeedback(ByVal [text] As String)

    Private UIUpdateThread As Thread = Nothing

    Private Sub DalEvents(ByVal DALmessage As String) Handles MyDAL.Feedback
        Select Case DALmessage
            Case "Dataset #1 has been added."
                DALmessage = "Stage 1 of 8 completed." & Environment.NewLine _
                    & "Table data has been gathered. Now collecting column data."

            Case "Dataset #2 has been added."
                DALmessage = "Stage 2 of 8 completed." & Environment.NewLine _
                    & "Column data has been gathered. Now collecting relationship data."

            Case "Dataset #3 has been added."
                DALmessage = "Stage 3 of 8 completed." & Environment.NewLine _
                    & "Relationship data has been gathered. Now collecting procedure data."

            Case "Dataset #4 has been added."
                DALmessage = "Stage 4 of 8 completed." & Environment.NewLine _
                    & "Procedure data has been gathered. Now collecting object xref data."

            Case "Dataset #5 has been added."
                DALmessage = "Stage 5 of 8 completed." & Environment.NewLine _
                    & "Object xref data has been gathered. Now collecting trigger data."

            Case "Dataset #6 has been added."
                DALmessage = "Stage 6 of 8 completed." & Environment.NewLine _
                    & "Trigger data has been gathered. Now collecting parameter data."

            Case "Dataset #7 has been added."
                DALmessage = "Stage 7 of 8 completed." & Environment.NewLine _
                    & "All data received, now writing to Excel."
        End Select

        Me.UIUpdateThread = New Thread( _
            New ParameterizedThreadStart(AddressOf Me.ThreadProcSafe))

        Me.UIUpdateThread.Start(DALmessage)
    End Sub

    Private Sub ThreadProcSafe(ByVal DALmessage As String)
        Me.SetText(DALmessage)
    End Sub

    Private Sub SetText(ByVal [text] As String)
        If Me.lblFeedback.InvokeRequired Then
            Dim d As New SetFeedback(AddressOf SetText)
            Me.Invoke(d, New Object() {[text]})
        Else
            Me.lblFeedback.Text = [text]
        End If
    End Sub

#End Region

#Region " BackgroundWorker Methods "
    Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim worker As System.ComponentModel.BackgroundWorker = _
            CType(sender, System.ComponentModel.BackgroundWorker)

        Dim lShouldReturn As Boolean
        GatherAndReportData(lShouldReturn)
        If lShouldReturn Then
            Return
        End If

        MessageBox.Show("Data Dictionary created in " & lblRunning.Text & ".", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        GroupBox1.Visible = True
        Timer1.Enabled = False
        lblFeedback.Text = ""
        Me.Cursor = Cursors.Default
    End Sub
#End Region

#Region " Event Handlers "
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Show()
        Me.Cursor = Cursors.WaitCursor
        Dim servers As DataTable
        servers = SmoApplication.EnumAvailableSqlServers(False)
        For Each drServer As DataRow In servers.Rows
            ComboBox1.Items.Add(drServer("Name"))
        Next
        ComboBox1.Text = "<Select a server...>"
        TextBox1.Text = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        TextBox2.Text = DataSource & InitialCatalog & IntegratedSecurity
        Me.Cursor = Cursors.Default
        'http://www.codeguru.com/vb/gen/vb_database/article.php/c5139
    End Sub

    Private Sub ComboBox1_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles ComboBox1.Leave, _
            ComboBox1.SelectedIndexChanged

        GetDatabases()

        If ComboBox1.Text <> "<Select a server...>" And ComboBox2.Text <> "" Then
            Button2.Enabled = True
        Else
            Button2.Enabled = False
        End If
    End Sub

    Private Sub ComboBox2_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles ComboBox2.Leave, _
            ComboBox2.SelectedIndexChanged

        TextBox2.Text = DataSource & ComboBox1.Text & InitialCatalog & ComboBox2.Text & IntegratedSecurity

        TextBox1.Text = My.Computer.FileSystem.SpecialDirectories.MyDocuments _
            & "\" & ComboBox1.Text & "-" & ComboBox2.Text & "-DataDictionary.xlsx"

        If ComboBox1.Text <> "<Select a server...>" And ComboBox2.Text <> "" Then
            Button2.Enabled = True
        Else
            Button2.Enabled = False
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim SaveFileDialog As New SaveFileDialog
        SaveFileDialog.FileName = ComboBox1.Text & "-" & ComboBox2.Text & "-DataDictionary.xlsx"
        SaveFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        SaveFileDialog.Filter = "Excel Workbooks (*.xlsx)|*.xlsx|All Files (*.*)|*.*"

        If (SaveFileDialog.ShowDialog(Me) <> System.Windows.Forms.DialogResult.OK) Then
            'user canceled out
            Exit Sub
        End If

        TextBox1.Text = SaveFileDialog.FileName
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim answer As DialogResult = _
            MessageBox.Show("Are you sure you want to generate the data dictionary?", _
            "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

        If answer = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Me.Cursor = Cursors.WaitCursor
        lblFeedback.Text = "Now collecting table and column data."
        lblStarted.Text = Date.Now.ToString
        Timer1.Enabled = True
        GroupBox1.Visible = False
        BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If BackgroundWorker1.IsBusy Then
            Dim answer As DialogResult = MessageBox.Show("Results are still being gathered." & Environment.NewLine _
                      & "Are you sure you want to quit?", "Question", _
                      MessageBoxButtons.YesNo, MessageBoxIcon.Question)

            If answer = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If

            BackgroundWorker1.CancelAsync()
        End If
        Me.Close()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If ComboBox1.Text = "<Select a server...>" Or ComboBox2.Text = "" Then
            Exit Sub
        End If

        'verify the server version
        Dim GoodServerVersion As Boolean

        Dim NewDAL As New DAL(TextBox2.Text)
        MyDAL = NewDAL
        Dim SQL As String = "SELECT [Version] = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)"
        Dim dt As DataTable = MyDAL.GetDataTable(SQL)

        If dt.Rows.Count > 0 Then
            Dim ServerVersion = dt.Rows(0).Item("Version").ToString

            Dim TestInt As Integer = ServerVersion.Split(".")(0)

            If TestInt > 8 Then
                GoodServerVersion = True
                DoInstall()
            Else
                GoodServerVersion = False
                MessageBox.Show("You cannot install the stored procedures" & Environment.NewLine _
                                & "into this version " & TestInt.ToString & " database.", "Error", _
                                MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End If

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        If ComboBox1.Text = "<Select a server...>" Or ComboBox2.Text = "" Then
            Exit Sub
        End If

        Dim ExclusionForm As New frmExclusions
        ExclusionForm.CnnString = Me.TextBox2.Text
        ExclusionForm.ShowDialog()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If ComboBox1.Text = "<Select a server...>" Or ComboBox2.Text = "" Then
            Exit Sub
        End If

        Dim answer As DialogResult = _
            MessageBox.Show("Are you sure you want to uninstall the stored procedures?", _
                            "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

        If answer = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Dim SProcScript As String() = Strings.Split(My.Resources.Uninstall, ";")
        Dim CMDs As New Generic.SortedList(Of Integer, String)
        For Each Str As String In SProcScript
            CMDs.Add(CMDs.Count, Str)
        Next

        Dim MyDAL As New DAL(TextBox2.Text)
        Dim result As Object = MyDAL.PerformBulkCommands(CMDs, False)

        If result Is Nothing Then
            MessageBox.Show("The ability to generate a data dictionary has been removed from this database.", _
                            "Note", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            MessageBox.Show("There was an error:" & Environment.NewLine & result.ToString, _
                            "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim AboutForm As New AboutBox1
        AboutForm.ShowDialog()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Dim TimeNow As DateTime = Date.Now.ToString

        Dim RunningTime As TimeSpan = TimeNow.Subtract(CDate(lblStarted.Text))

        lblRunning.Text = String.Format("{0} Hours, {1} minutes, {2} seconds", _
                                        RunningTime.Hours.ToString, _
                                        RunningTime.Minutes.ToString, _
                                        RunningTime.Seconds.ToString)
    End Sub
#End Region

#Region " Other Methods "
    Private Sub GetDatabases()
        If ComboBox1.Text <> "" And ComboBox1.Text <> "<Select a server...>" Then
            Me.ComboBox2.Items.Clear()

            Dim theServerInfo As New Server(New ServerConnection(ComboBox1.Text))
            For Each currentDatabase As Database In theServerInfo.Databases
                Me.ComboBox2.Items.Add(currentDatabase.Name)
            Next
        End If

        TextBox2.Text = DataSource & ComboBox1.Text & InitialCatalog & IntegratedSecurity
    End Sub

    Private Sub GatherAndReportData(ByRef shouldReturn As Boolean)
        shouldReturn = False

        Dim NewDAL As New DAL(TextBox2.Text)
        MyDAL = NewDAL
        Dim Datasets As Generic.SortedList(Of Integer, DataTable) = MyDAL.GetMultipleDataTables("EXEC dbo.GenerateDataDictionary")
        If Datasets Is Nothing Then
            MessageBox.Show("Unable to gather results.  Is the stored procedure installed?", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            shouldReturn = True : Exit Sub
        End If

        Dim iRowCount As Integer, iColCount As Integer
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet1 As Object, oSheet2 As Object, oSheet3 As Object, oSheet4 As Object, _
            oSheet5 As Object, oSheet6 As Object, oSheet7 As Object
        oExcel = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"))
        oBook = oExcel.Workbooks.Add

        oSheet1 = oBook.Worksheets(1)
        With oSheet1
            .Name = "Tables"
            .Range("A1").Value = "Table Name"
            .Range("B1").Value = "Description"
            .Range("C1").Value = "Type"
            .Range("A1:C1").Font.Bold = True
            iRowCount = Datasets(0).Rows.Count
            iColCount = Datasets(0).Columns.Count
            If iRowCount > 0 Then
                Try
                    .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(0))
                    .Range("A1:C" & (iRowCount + 1).ToString).AutoFilter()
                    .Range("A1:C" & (iRowCount + 1).ToString).EntireColumn.Columns.AutoFit()
                    .Activate()
                    .Range("A2").Select()
                    .Application.ActiveWindow.FreezePanes = True
                Catch e As Exception
                    MessageBox.Show("Something went boom:" & Environment.NewLine _
                                    & Environment.NewLine _
                                    & e.Message _
                                    & Environment.NewLine _
                                    & e.StackTrace, "You Did Something Very Bad", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End If
        End With

        oSheet2 = oBook.Worksheets(2)
        With oSheet2
            .Name = "Columns"
            .Range("A1").Value = "Column"
            .Range("B1").Value = "Table"
            .Range("C1").Value = "Column Position"
            .Range("D1").Value = "Data Type"
            .Range("E1").Value = "Default Value"
            .Range("F1").Value = "Nullable"
            .Range("G1").Value = "Description"
            .Range("A1:G1").Font.Bold = True
            iRowCount = Datasets(1).Rows.Count
            iColCount = Datasets(1).Columns.Count
            If iRowCount > 0 Then
                .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(1))
                .Range("A1:G" & (iRowCount + 1).ToString).AutoFilter()
            End If
            .Range("A1:G" & (iRowCount + 1).ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet3 = oBook.Worksheets(3)
        With oSheet3
            .Name = "Relationships"
            .Range("A1").Value = "Constraint"
            .Range("B1").Value = "Type"
            .Range("C1").Value = "Table"
            .Range("D1").Value = "Column"
            .Range("E1").Value = "Constrained By"
            .Range("F1").Value = "Description"
            .Range("G1").Value = "Delete Action"
            .Range("H1").Value = "Update Action"
            .Range("A1:H1").Font.Bold = True
            iRowCount = Datasets(2).Rows.Count
            iColCount = Datasets(2).Columns.Count
            If iRowCount > 0 Then
                .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(2))
                .Range("A1:H" & (iRowCount + 1).ToString).AutoFilter()
            End If
            .Range("A1:H" & (iRowCount + 1).ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet4 = oBook.Worksheets.Add()
        With oSheet4
            .Move(After:=oBook.Worksheets(oBook.Worksheets.Count))
            .Name = "Procedures"
            .Range("A1").Value = "Procedure Name"
            .Range("A1:A1").Font.Bold = True
            iRowCount = Datasets(3).Rows.Count
            iColCount = Datasets(3).Columns.Count
            If iRowCount > 0 Then
                .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(3))
                .Range("A1:A" & (iRowCount + 1).ToString).AutoFilter()
            End If
            .Range("A1:A" & (iRowCount + 1).ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet5 = oBook.Worksheets.Add()
        With oSheet5
            .Move(After:=oBook.Worksheets(oBook.Worksheets.Count))
            .Name = "Proc Object XRef"
            .Range("A1").Value = "Stored Procedure or View Name"
            .Range("B1").Value = "Object Name"
            .Range("C1").Value = "Object Type"
            .Range("A1:C1").Font.Bold = True
            iRowCount = Datasets(4).Rows.Count
            iColCount = Datasets(4).Columns.Count
            If iRowCount > 0 Then
                .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(4))
                .Range("A1:C" & (iRowCount + 1).ToString).AutoFilter()
            End If
            .Range("A1:C" & (iRowCount + 1).ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet6 = oBook.Worksheets.Add()
        With oSheet6
            .Move(After:=oBook.Worksheets(oBook.Worksheets.Count))
            .Name = "Triggers"
            .Range("A1").Value = "Trigger Name"
            .Range("B1").Value = "Type"
            .Range("C1").Value = "Parent Name"
            .Range("A1:C1").Font.Bold = True
            iRowCount = Datasets(5).Rows.Count
            iColCount = Datasets(5).Columns.Count
            If iRowCount > 0 Then
                .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(5))
                .Range("A1:C" & (iRowCount + 1).ToString).AutoFilter()
            End If
            .Range("A1:C" & (iRowCount + 1).ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet7 = oBook.Worksheets.Add()
        With oSheet7
            .Move(After:=oBook.Worksheets(oBook.Worksheets.Count))
            .Name = "Parameters"
            .Range("A1").Value = "Parent Name"
            .Range("B1").Value = "Param Name"
            .Range("C1").Value = "Direction"
            .Range("D1").Value = "Position"
            .Range("E1").Value = "Type"
            .Range("A1:E1").Font.Bold = True
            iRowCount = Datasets(6).Rows.Count
            iColCount = Datasets(6).Columns.Count
            If iRowCount > 0 Then
                .Range("A2").Resize(iRowCount, iColCount).Value = GetDataAsObject(iRowCount, iColCount, Datasets(6))
                .Range("A1:E" & (iRowCount + 1).ToString).AutoFilter()
            End If
            .Range("A1:E" & (iRowCount + 1).ToString).EntireColumn.Columns.AutoFit()
            .Activate()
            .Range("A2").Select()
            .Application.ActiveWindow.FreezePanes = True
        End With

        oSheet1.Activate()

        Try
            oBook.SaveAs(TextBox1.Text)
        Catch ex As Exception
            Dim answer As DialogResult = MessageBox.Show("There was a problem saving the file." & Environment.NewLine _
                      & "Do you want to see the error trace?", "Error", _
                      MessageBoxButtons.YesNo, MessageBoxIcon.Question)

            If answer = Windows.Forms.DialogResult.Yes Then
                MessageBox.Show(ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End Try

        'kill the objects
        oSheet1 = Nothing
        oSheet2 = Nothing
        oSheet3 = Nothing
        oSheet4 = Nothing
        oSheet5 = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
    End Sub

    Private Shared Function GetDataAsObject(ByVal iRowCount As Integer, ByVal iColCount As Integer, ByVal dt As DataTable) As Object(,)
        Dim oData(iRowCount, iColCount) As Object, iRow As Integer, iCol As Integer

        For iRow = 0 To iRowCount - 1
            For iCol = 0 To iColCount - 1
                oData(iRow, iCol) = dt.Rows(iRow).Item(iCol)
            Next
        Next
        Return oData
    End Function

    Private Sub DoInstall()
        Dim answer As DialogResult = _
                            MessageBox.Show("Are you sure you want to install or re-install the stored procedures?", _
                                            "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

        If answer = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Dim SProcScript As String() = Strings.Split(My.Resources.Uninstall, ";")
        Dim CMDs As New Generic.SortedList(Of Integer, String)
        For Each Str As String In SProcScript
            CMDs.Add(CMDs.Count, Str)
        Next

        SProcScript = Strings.Split(My.Resources.Install, ";")
        For Each Str As String In SProcScript
            CMDs.Add(CMDs.Count, Str)
        Next

        Dim MyDAL As New DAL(TextBox2.Text)
        Dim result As Object = MyDAL.PerformBulkCommands(CMDs, False)

        If result Is Nothing Then
            MessageBox.Show("The ability to generate a data dictionary has been installed into this database.", _
                            "Note", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            MessageBox.Show("There was an error:" & Environment.NewLine & result.ToString, _
                            "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    End Sub
#End Region
End Class
